#!/usr/bin/python
import sys
import MySQLdb
import yeast_util
import os
import time
import ConfigParser

config = ConfigParser.RawConfigParser()
config.read('./rfex_sql.config')
myhost = config.get("mysql_jdbc_configs", "host")
mydb = config.get("mysql_jdbc_configs", "db")
myuser = config.get("mysql_jdbc_configs", "username")
mypw = config.get("mysql_jdbc_configs", "password")

conn = MySQLdb.connect (host = myhost,
                           user = myuser,
                           passwd = mypw,
                           db = mydb)
cursor = conn.cursor ()
args = sys.argv
schema = 'omics'
associations_table = "_associations"
features_table = "_features"
feature_helper = {}
feature_helper["IMP2"] = "C:GENO:IMP2:chrIX:53981:55021:-"
feature_helper["AAP1"] = "C:GENO:AAP1:chrVIII:198740:201310:-"
infile = ""
dataset_label = ""
if (len(args) == 3):
	infile = args[1]
	dataset_label = args[2]
	associations_table = schema + "." + dataset_label + associations_table
	features_table = schema + "." + dataset_label + features_table
else:
	print "Usage is py2.6 parse_associations_kruglyak.py network_associations.tsv datasetlabel feature_table"
	sys.exit(1)

print "Parsing associations kicked off %s" %time.ctime()

fin = open(infile,'r')
fshout = open('load_sql_associations_' + dataset_label + '.sh','w')
tsvout = open(dataset_label + '_links.tsv','w')
sqlout = open(dataset_label + '_network_features_update0.sql','w')
sqlout.write("use omics;\n");
featureid = -1
rank = -1
weight = -1
updateCount = 0

fshout.write("#!/bin/bash\n")
fshout.write("mysql --user=dudley_omics --password=dud1ey --database=omics<<EOFMYSQL\n")
fshout.write("load data local infile './" + dataset_label + "_links.tsv' replace INTO TABLE " + associations_table + " fields terminated by '\\t' LINES TERMINATED BY '\\n';")
fshout.write("\nEOFMYSQL\n")
fshout.write('date\n')
fshout.write('echo exec update sql iteration 0\n')
fshout.write('mysql -ududley_omics -pdud1ey < ' + dataset_label + '_network_features_update0.sql\n')

#select id from coad_read_features_u where alias = 'N:CLIN:preoperative_pretreatment_cea_level::::'
#could have done this too
#update coad_read_networks set feature1id = (select id from coad_read_features_u where alias = 'N:METH:cg00002426:chr3:57718583::'), feature2id = (select id from coad_read_features_u where alias = 'N:METH:cg10398950:chr17:34871980::') where alias1 = 'N:METH:cg00002426:chr3:57718583::' and alias2 = 'N:METH:cg10398950:chr17:34871980::';
def getFeatureId(alias):
	global featureid
	cursor.execute ("SELECT id FROM " + features_table + " WHERE alias = '" + alias + "'")
	#'SELECT VERSION()")
	row = cursor.fetchone ()
	rows = cursor.fetchall ()
	if (cursor.rowcount != 1):
		print alias + " feature id count " + str(cursor.rowcount)
		return -1 
	return row[0]

def getFeatureRankWeight(alias):
        global featureid, rank, weight
        cursor.execute ("SELECT rank, weight, id FROM " + features_table + " WHERE alias = '" + alias + "'")
        row = cursor.fetchone ()
        #rows = cursor.fetchall ()
        if (cursor.rowcount != 1):
                print alias + " feature id count " + str(cursor.rowcount)
                return -1
        rank = 0 
	if (row[0] != None):
		rank = row[0]
	weight = 0
	if (row[1] != None):
		weight = row[1]
	return row[2]

lc = 0
def updateNetworkFeature(updateSql):
	global updateCount
        print updateSql
        cursor.execute(updateSql)
        print "line no %d updated: %d" % (lc, cursor.rowcount)

lines = fin.readlines()
fin.close()

for line in lines:
	lc = lc + 1
	columns = line.split('\t')
	f1alias = columns[0]
	f2alias = columns[1]
	if (f1alias.find('PHENO') != -1):
		f1alias = yeast_util.transPhenoFeature(f1alias)
	if (f2alias.find('PHENO') != -1):
		f2alias = yeast_util.transPhenoFeature(f2alias)	
	pvalue = columns[2]
	importance = columns[3]
	correlation = columns[4]
	fields=line.strip().replace('\t',':').replace(':','\t')
	data = fields.split('\t')
	if len(data) > 4 and len(data[3]) > 3:
		data[3] = data[3][3:]		
	if len(data) > 10 and len(data[10]) > 3:
		data[10] = data[10][3:]		
	#fout.write("\t".join(data) + "\n")
	if (float(pvalue) >= 0):
		#print f1alias + "\t" + f2alias + "\t" + importance + "\t" + pvalue + "\t" + correlation
		if (f1alias.find('IMP2') != -1):
			f1alias = feature_helper.get("IMP2")
		elif (f2alias.find('IMP2') != -1):
			f2alias = feature_helper.get("IMP2")
		elif (f1alias.find('AAP1') != -1):
			f1alias = feature_helper.get("AAP1")
		elif (f2alias.find('AAP1') != -1):
			f2alias = feature_helper.get("AAP1")
		#inserted 0 as value place holder for importance
		tsvout.write(f1alias + "\t" + f2alias + "\t" + pvalue + "\t" + importance + "\t" + correlation)
		#update coad_read_networks set feature1id = 99, feature2id = -1 where alias1 = 'N:CLIN:preoperative_pretreatment_cea_level::::' and alias2 = 'N:METH:cg10398950:chr17:34871980::'
		f1id = getFeatureId(f1alias)
		f2id = getFeatureRankWeight(f2alias)
		if (f2id != -1 and f1id != -1):
			updateCount = updateCount + 1
			#updateNetworkFeature("update " + table + " set feature1id = " + str(f1id) + ", feature2id=" + str(f2id) + " where alias1='" + f1alias + "' and alias2='" + f2alias + "'\n")
			f2fa = f2alias.split(':')
			label = "null"
			if (f2fa[2] != ""):
				label = f2fa[2]
			chr = "null"
			start = "0"
			end = "0"
                	if (f2alias.find('PHENO') == -1):
				if (f2fa[3] != ""):
                        		chr = f2fa[3].split("chr")[1]
                		if (f2fa[4] != ""):
                        		start = f2fa[4]
                		if (f2fa[5] != ""):
                        		end = f2fa[5] 

			if (updateCount > 100000):
				iter = lc/updateCount
				print("new iteration:" + str(iter))
				updateCount = 0
				sqlout.close()
				sqlout = open(dataset_label + '_network_features_update' + str(iter) +'.sql','w')
				sqlout.write("use omics;\n");
				fshout.write('date\n')
				fshout.write('echo exec update sql iteration ' + str(iter) + '\n')
				fshout.write('mysql -ududley_omics -pdud1ey < ' + dataset_label + '_network_features_update' + str(iter) +'.sql\n')
			#updateNetworkFeature("update " + associations_table + " set f2type = '" + str(f2fa[0]) + "', f2source='" + str(f2fa[1]) + "', f2label='" + label + "', f2chr='" + str(chr) + "', f2start=" + str(start) + ", f2end=" + str(end) + ", f2rank=" + str(rank) + ", f2weight=" + str(weight) + " where alias1='" + f1alias + "' and alias2='" + f2alias + "'")
			sqlout.write("update " + associations_table + " set f2type = '" + str(f2fa[0]) + "', f2source='" + str(f2fa[1]) + "', f2label='" + label + "', f2chr='" + str(chr) + "', f2start=" + str(start) + ", f2end=" + str(end) + ", f2rank=" + str(rank) + ", f2weight=" + str(weight) + " where alias1='" + f1alias + "' and alias2='" + f2alias + "';\n")
			sqlout.write("update " + associations_table + " set feature1id = " + str(f1id) + ", feature2id=" + str(f2id) + " where alias1='" + f1alias + "' and alias2='" + f2alias + "';\n")

tsvout.close()
sqlout.close()
cursor.close()
conn.close()
fshout.close()
fshout.close()
print "Done Parsing and building association sql inserts and updates%s\nRunning bulk load and update on mysql\nos.system sh %s" %(time.ctime(), fshout.name)
os.system("sh " + fshout.name)
